unit uAcomProd;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, FMTBcd, DB, DBClient, Provider, SqlExpr, Grids, Wwdbigrd,
  Wwdbgrid, StdCtrls, Buttons, ComCtrls, EditDate, ExtCtrls, wwSpeedButton,
  wwDBNavigator, wwclearpanel, wwdbdatetimepicker, DBGrids, JvExDBGrids,
  JvDBGrid, JvComponentBase, JvDBGridExport,ShellApi;

type
  TfAcomProd = class(TForm)
    SpAcomProd: TSQLStoredProc;
    sds: TSQLDataSet;
    dsp: TDataSetProvider;
    cds: TClientDataSet;
    DataSource1: TDataSource;
    Panel1: TPanel;
    btnProcurar: TBitBtn;
    Label1: TLabel;
    Label2: TLabel;
    BitBtn1: TBitBtn;
    wwDBGrid1: TwwDBGrid;
    EDtIni: TEditDate;
    EDtFim: TEditDate;
    SaveDialog1: TSaveDialog;
    JvDBGridExcelExport1: TJvDBGridExcelExport;
    procedure btnProcurarClick(Sender: TObject);
    procedure BitBtn1Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  fAcomProd: TfAcomProd;

implementation

uses udm;

{$R *.dfm}

procedure TfAcomProd.btnProcurarClick(Sender: TObject);
begin
  cds.Close;
  {cds.CommandText := 'SELECT * FROM (  ' +
    'SELECT CLIENTE, RAZAO_SOCIAL, VAL_PROGRAMA, VAL_FATURADO,VAL_PROGRAMA- ' +
    'VAL_FATURADO A_FATURAR, VAL_FATURADO / VAL_PROGRAMA * 100 PER_FAT, ' +
    'VALOR_ESTOQUE PROD_REALIZADA, (VALOR_ESTOQUE + VAL_FATURADO) /  ' +
    'VAL_PROGRAMA * 100 PER_FAT_PROD ' +
    ' FROM ( ' +
    'SELECT CLIENTE, RAZAO_SOCIAL, SUM(VAL_PROGRAMA) VAL_PROGRAMA, SUM(VAL_FATURADO) ' +
    'VAL_FATURADO, SUM(VALOR_SALDO) VALOR_ESTOQUE FROM ' +
    ' ( ' +
    'SELECT CLIENTE, RAZAO_SOCIAL, PRODUTO, ' +
    'SUM(QTD_PROGRAMA) QTD_PROGRAMA, SUM(VAL_PROGRAMA) VAL_PROGRAMA, ' +
    'SUM(VENDA_BRUTA) VENDA_BRUTA, SUM(QTD_FATURADA) QTD_FATURADA, SUM(VAL_FATURADO) '  +
    'VAL_FATURADO, SUM(FATURAMENTO_BRUTO) FAT_BRUTO '  +
    ' FROM ' +
    ' ( '  +
    ' SELECT EMP_CODEMP CLIENTE, EMP_ERAZAO RAZAO_SOCIAL, PRV_CODPRO PRODUTO, TRUNC(PRV_DTPROG) DATA, '  +
    ' GENERICA.CALCLANC( trunc(SYSDATE), PDV_CODUNI, 999, ITV_VALITE ) VALOR_ITEM, '  +
    ' PRV_QTPROG QTD_PROGRAMA, PRV_QTPROG * GENERICA.CALCLANC( trunc(SYSDATE), PDV_CODUNI, ' +
    ' 999, ITV_VALITE ) VAL_PROGRAMA, ' +
    ' ((PRV_QTPROG * GENERICA.CALCLANC( trunc(SYSDATE), PDV_CODUNI, 999, ITV_VALITE )) + ITV_VALIPI) VENDA_BRUTA, ' +
    ' 0 QTD_FATURADA, 0 VAL_FATURADO, 0 FATURAMENTO_BRUTO '  +
    ' FROM '  +
    ' F_PRGVEN, F_PEDVENDA, F_PEDITEM,F_CDEMP '  +
    ' WHERE PRV_NUMPED = PDV_NUMPED ' +
    ' AND   PRV_NUMPED = ITV_NUMPED ' +
    ' AND   PRV_CODPRO = ITV_CODPRO ' +
    ' AND   EMP_CODEMP = PRV_CODEMP ' +
    ' AND   PDV_STATUS not in (' + QuotedStr('R') + ' ,' +  QuotedStr('L') + ' , ' +
    QuotedStr('Z') +  ') ' +
    ' UNION ALL ' +
    ' SELECT MOV_CODEMP,EMP_ERAZAO , MOV_CODPRO,  TRUNC(MOV_DATMOV),MOV_VALUNI, ' +
    ' 0,0,0,MOV_QTDMOV,MOV_VALTOT - MOV_VALICM - MOV_VALPIS - MOV_COFINS, MOV_VALTOT + MOV_VALIPI ' +
    ' FROM F_MOVTO, F_CDEMP ' +
    ' WHERE MOV_CODEMP = EMP_CODEMP ' +
    '   AND MOV_TIPMOV IN (' + QuotedStr('SF') + ', ' + QuotedStr('SVS') + ', ' + QuotedStr('SFT') + ' )' +
    ' )  ' +
    ' WHERE TRUNC(DATA) BETWEEN TO_DATE(' + QuotedStr(EDtIni.Text) + ',' + QuotedStr('dd/mm/yyyy') +
	  ' )  AND TO_DATE(' + QuotedStr(EDtFim.Text) + ', ' + QuotedStr('dd/mm/yyyy') + ' )' +
    ' GROUP BY CLIENTE,RAZAO_SOCIAL, PRODUTO ), ' +
    ' ('  +
    ' SELECT A.*, ITV_VALITE * SALDO VALOR_SALDO FROM ' +
    ' (' +
    ' SELECT ITV_CODPRO CODPRO, MAX(ITV_NUMPED) NUMPED, NVL(SALDO,0) SALDO ' +
    ' FROM F_PEDVENDA, F_PEDITEM, '    +
    ' ( ' +
    ' SELECT EST_CODPRO, SUM(EST_SLDANT + EST_QTENTR - EST_QTCONS) SALDO ' +
    ' FROM F_ESTOQ, F_LOCEST ' +
    ' WHERE EST_LOCEST = LCE_CODIGO ' +
    ' AND LCE_LOCMRP = '  + QuotedStr('S') +
    ' GROUP BY EST_CODPRO ' +
    ' )' +
    ' WHERE PDV_NUMPED = ITV_NUMPED ' +
    ' AND   PDV_STATUS not in (' + QuotedStr('R') + ' ,' +  QuotedStr('L') + ' , ' +
    QuotedStr('Z') +  ') ' +
    ' AND   ITV_CODPRO = EST_CODPRO(+) ' +
    ' GROUP BY ITV_CODPRO, SALDO ' +
    ' ) A, F_PEDITEM ' +
    ' WHERE ITV_CODPRO = CODPRO ' +
    ' AND   ITV_NUMPED = NUMPED ' +
    ' ) '  +
    ' WHERE PRODUTO = CODPRO ' +
    ' GROUP BY CLIENTE, RAZAO_SOCIAL ' +
    ' ) ' +
    ' ) ';}
    cds.Params.ParamByName('v_datIni').AsString := EDtIni.Text;
    cds.Params.ParamByName('v_datFim').AsString := EDtFim.Text;
  cds.Open;
end;

procedure TfAcomProd.BitBtn1Click(Sender: TObject);
begin
  if (SaveDialog1.Execute) then
  begin
    wwDBGrid1.ExportOptions.FileName := SaveDialog1.FileName;
    //JvDBGridExcelExport1.FileName := SaveDialog1.FileName;
  end;
  wwdbgrid1.ExportOptions.Save;
  //JvDBGridExcelExport1.ExportGrid;
  //ShellExecute(Handle, nil, PChar(SaveDialog1.FileName), nil, nil, SW_SHOWNORMAL);
  MessageDlg('Arquivo exportado com sucesso.',
          mtConfirmation, [mbOk],0);
end;

end.
